package com.weng.druid.parse;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.hive.ast.HiveInsertStatement;
import com.alibaba.druid.sql.dialect.hive.parser.HiveStatementParser;
import com.alibaba.druid.sql.dialect.hive.stmt.HiveCreateTableStatement;
import com.alibaba.druid.sql.dialect.hive.visitor.HiveSchemaStatVisitor;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.weng.coder.common.utils.Constants;
import com.weng.coder.common.utils.DBType;
import com.weng.coder.common.utils.DbUtils;
import com.weng.coder.entity.CodeTable;
import com.weng.coder.entity.Field;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.util.*;

/**
 * @author wengzhonghui
 * @date 2021/1/13 16:57
 */
@Slf4j
public class ParseSqlTest1 {



		@Test
		public void testParse(){
				 // 可以是ORACLE、POSTGRESQL、SQLSERVER、ODPS等
//				String sql = "CREATE TABLE `platform_team` (   `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',   `team_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '团队名称',   `owner_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '-1' COMMENT '创建用户Id',   `owner_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建用户名称',   `is_del` tinyint(1) DEFAULT '0' COMMENT '删除状态1册除0未删除',   `create_time` datetime DEFAULT NULL COMMENT '创建时间',   `update_time` datetime DEFAULT NULL COMMENT '修改时间',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队信息';";
//				String sql1 = "CREATE TABLE `platform_team1` (   `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',   `team_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '团队名称',   `owner_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '-1' COMMENT '创建用户Id',   `owner_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建用户名称',   `is_del` tinyint(1) DEFAULT '0' COMMENT '删除状态1册除0未删除',   `create_time` datetime DEFAULT NULL COMMENT '创建时间',   `update_time` datetime DEFAULT NULL COMMENT '修改时间',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队信息';";
//				String sql2 = "SELECT `ID`,`project_id`,`NAME`,`CODE`,`PARENT_ID`,`POSITION`,d.`author_username`  FROM  `platform_manager202101`.`api_catalog` c INNER JOIN `api_doc` d ON c.`ID`= d.`id`;";
//				String sql3 = "CREATE TABLE test_metadata  AS  SELECT * FROM `md_collect_log` l INNER JOIN `md_collect_task` t ON l.id=t.id WHERE l.id='22'";
				String sql5 = "create table base_datameta1 as SELECT cata.NAME catalogName, p.progress, c.name FROM (\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_stu_rel stuRel ON c.id = stuRel.course_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.stu_id= '1'\n" +
								"            UNION\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_class_rel classRel ON classRel.course_id = c.id\n" +
								"            INNER JOIN edu_student_class_rel stuRel ON stuRel.class_id = classRel.class_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.user_id = '1'\n" +
								"        ) c\n" +
								"        LEFT JOIN edu_catalog cata ON c.catalog_id=cata.ID\n" +
								"        LEFT JOIN edu_course_study_progress p ON c.id = p.course_id AND p.stu_id = '1'\n" +
								"        WHERE 1=1 AND p.course_id = '11'";
				String sql6 = "INSERT INTO base_datameta1 (catalogName,progress,name)  SELECT cata.NAME catalogName, p.progress, c.name FROM (\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_stu_rel stuRel ON c.id = stuRel.course_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.stu_id= '1'\n" +
								"            UNION\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_class_rel classRel ON classRel.course_id = c.id\n" +
								"            INNER JOIN edu_student_class_rel stuRel ON stuRel.class_id = classRel.class_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.user_id = '1'\n" +
								"        ) c\n" +
								"        LEFT JOIN edu_catalog cata ON c.catalog_id=cata.ID\n" +
								"        LEFT JOIN edu_course_study_progress p ON c.id = p.course_id AND p.stu_id = '1'\n" +
								"        WHERE 1=1 AND p.course_id = '11'";
				String sql7 = "INSERT INTO sebsites(name, country)  SELECT name, country FROM apps";
				String sql8 = "insert into table_test_2 (farendma, hesuandm, hesuanmc, weihguiy, weihjigo, weihriqi, shijchuo) values\n" +
								"('99996','HS205301','代码1','S####','101001','20140101',1414673101376), \n" +
								"('99996','HS205401','代码2','S####','101001','20140101',1414673101376);";

				// 方法1 建议
				getTablesBySQL(sql5);
//				// 方法2
//				getTablesBySQL1(sql5);
//				// 获取列的场景：
//				getColumnByInsertSQL(sql7);

		}


		public void getTablesBySQL(String sql){
				MySqlStatementParser  parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlSchemaStatVisitor  visitor = new MySqlSchemaStatVisitor ();
				sqlStatement.accept(visitor);
				Map<TableStat.Name, TableStat> tableStatMap = visitor.getTables();
				for(Map.Entry<TableStat.Name, TableStat> tableStatEntry: tableStatMap.entrySet()){
						System.out.println("表名：" + tableStatEntry.getKey().getName());
						System.out.println("操作名：" + tableStatEntry.getValue());
				}
		}

		public void getTablesBySQL1(String sql){
				final String dbType = JdbcConstants.HIVE;
				List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
				for(SQLStatement sqlStatement : stmtList){
						if(sqlStatement instanceof HiveCreateTableStatement){
								HiveCreateTableStatement hiveCreateTableStatement = (HiveCreateTableStatement)sqlStatement;
								SQLSelect sqlSelect = hiveCreateTableStatement.getSelect();
								SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock)sqlSelect.getQuery();
								// 查询的列
								List<SQLSelectItem> selectList = sqlSelectQuery.getSelectList();
								// 来源表
								Set<String> sourceTables = new HashSet<>();
								if(sqlSelectQuery.getFrom()!=null){
										SQLTableSource from =  sqlSelectQuery.getFrom();
										getTableBySQLTableSource(from, sourceTables);
								}
								// 目标表
								String targetTableName = hiveCreateTableStatement.getTableSource().getExpr().toString();
								System.out.println(targetTableName);
								System.out.println(sourceTables);
						}
				}
		}

		// 获取插入SQL的列
		public void getColumnByInsertSQL(String sql){
				MySqlStatementParser parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlInsertStatement  insertStatement = (MySqlInsertStatement )sqlStatement;
				System.out.println("表名：" + insertStatement.getTableName());
				//获取列的名称
				List<SQLExpr> columnExprs = insertStatement.getColumns();
				System.out.println("列的名称为：");
				for(SQLExpr expr : columnExprs){
						System.out.print(expr + "\t");
				}
		}


		private Set<String> getTableBySQLTableSource(SQLTableSource sqlTableSource, Set<String>  tableList){
				if(sqlTableSource != null){
						if(sqlTableSource instanceof SQLJoinTableSource){
								SQLJoinTableSource joinFrom = (SQLJoinTableSource) sqlTableSource;
								getTableBySQLTableSource(joinFrom.getLeft(), tableList);
								getTableBySQLTableSource(joinFrom.getRight(), tableList);
						}else if(sqlTableSource instanceof SQLUnionQueryTableSource){
								SQLUnionQueryTableSource unionFrom = (SQLUnionQueryTableSource) sqlTableSource;
								SQLUnionQuery sqlUnionQuery = unionFrom.getUnion();
								getTableBySQLSelectQuery(sqlUnionQuery.getLeft(), tableList);
								getTableBySQLSelectQuery(sqlUnionQuery.getRight(), tableList);
						}else if(sqlTableSource instanceof SQLExprTableSource){
								SQLExprTableSource sqlExprTableSource = (SQLExprTableSource)sqlTableSource;
								tableList.add(sqlExprTableSource.getExpr().toString());
						}
				}
				return tableList;
		}

		private Set<String> getTableBySQLSelectQuery(SQLSelectQuery sqlSelectQuery, Set<String>  tableList){
				if(sqlSelectQuery != null){
						if(sqlSelectQuery instanceof  SQLSelectQueryBlock){
								SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock)sqlSelectQuery;
								SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
								getTableBySQLTableSource(sqlTableSource,tableList);
						}
				}
				return tableList;
		}



}
